package yu.zhou.njupt.pdsmigration.processers.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.lang3.StringUtils;

import yu.zhou.njupt.pdsmigration.processers.Processor;
import yu.zhou.njupt.pdsmigration.utils.StringUtil;

import com.google.common.base.Joiner;

/**
 * @author yu.zhou
 * 
 */
public class LegendUsageProcessor implements Processor {

    public void process(Connection connection) throws Exception {
        try {
            UpdateLegendUsage(connection);//update usage_note
            buildNewLegendUsage(connection);//build new legend usage
            System.out.println("LegendUsageProcessor Complete!");
        } catch (Exception e) {
            System.out.println("LegendUsageProcessor Failed!");
            throw e;
        }
    }

    private void UpdateLegendUsage(Connection connection) throws Exception {
        Statement s = null;
        Statement s_ = null;
        ResultSet rs = null;
        ResultSet rs_ = null;
        QueryRunner sqlRunner = null;
        String updateString = "UPDATE NEW_EPC_LEGEND_USAGE SET MODEL_OPTION_CODES = ? WHERE ID = ?";
        String queryOCString = "SELECT COUNT(*) FROM NEW_EPC_OPTION WHERE CODE = ";
        List<String[]> dataList = new ArrayList<String[]>();
        String[][] data = null;

        try {
            s = connection.createStatement();
            s_ = connection.createStatement();
            rs = s.executeQuery("SELECT * FROM NEW_EPC_LEGEND_USAGE");
            sqlRunner = new QueryRunner();
            while (rs.next()) {
                String id = rs.getString("ID");
                String usageNoteString = rs.getString("USAGE_NOTE");
                if (StringUtils.isNotBlank(usageNoteString)) {// usage_note未空的,按照;切分,然后逗号分割放入model_option_codes字段
                    List<String> usageNotes = new ArrayList<String>();
                    for (String str : usageNoteString.split(";")) {
                        int count = 0;
                        rs_ = s_.executeQuery(queryOCString + "'" + str + "'");
                        if (rs_.next()) {
                            count = rs_.getInt(1);
                        }
                        if (count != 0) {
                            usageNotes.add(str);
                        }
                    }
                    dataList.add(new String[] { Joiner.on(",").join(usageNotes.toArray()), id });
                }// usage==null的,sql中直接导入
            }
            
            //update
            data = new String[dataList.size()][];
            for (int i = 0; i < dataList.size(); i++) {
                data[i] = dataList.get(i);
            }
            sqlRunner.batch(connection, updateString, data);
            
        } catch (Exception e) {
            throw e;
        } finally {
            DbUtils.closeQuietly(null, s_, rs_);
            DbUtils.closeQuietly(null, s, rs);
        }
    }

    private void buildNewLegendUsage(Connection connection) throws Exception {
        Statement queryLUstatement = null; // 查询NEW_EPC_LEGEND_USAGE(用法表)
        Statement queryMCstatement = null; // 根据NEW_EPC_LEGEND_USAGE每条记录的image_code寻找model_code
        ResultSet rsOfLU = null;
        ResultSet rsOfMC = null;
        QueryRunner sqlRunner = null;
        String[] columnsOfLU = new String[] { "MODEL_CODE", "GROUP_CODE", "IMAGE_CODE", "PART_NAME", "PART_NAME_",
                "QTY", "CALLOUT", "SUB_SORT", "USAGE_NOTE", "USAGE_NOTE_", "MODEL_OPTION_CODES", "PART_NO", "SORT",
                "SERIES_CODE", "IS_ACCESSORIES" };
        String insertString = "INSERT INTO NEW_EPC_LEGEND_USAGE_NEW (" + Joiner.on(",").join(columnsOfLU)
                + ") VALUES (" + StringUtil.getPlaceHolderStr(columnsOfLU.length) + ")";
        String searchModelCodeString = "SELECT APPLICABLE_MODEL FROM NEW_EPC_IMAGE_EXPAND WHERE CODE = ";
        
        List<String[]> insertDataList = new ArrayList<String[]>();
        String[][] insertData = null;

        try {
            queryLUstatement = connection.createStatement();
            queryMCstatement = connection.createStatement();
            rsOfLU = queryLUstatement.executeQuery("SELECT * FROM NEW_EPC_LEGEND_USAGE");
            sqlRunner = new QueryRunner();
            while (rsOfLU.next()) {
                String imageCode = rsOfLU.getString("IMAGE_CODE");
               
                /* 为每条用法表记录找对应的MODEL_CODE */
                if (imageCode != null) {
                    rsOfMC = queryMCstatement.executeQuery(searchModelCodeString + "'" + imageCode + "'");
                }
                String[] lUWithoutModelCode = new String[columnsOfLU.length]; // lUWithoutModelCode为没有
                for (int i = 0; i < columnsOfLU.length; i++) { // MODEL_CODE的一条用法表记录
                    lUWithoutModelCode[i] = rsOfLU.getString(columnsOfLU[i]);
                }
                
                boolean hasRow = false;
                
                String[] lU = null;
                while (rsOfMC.next()) {
                	hasRow  = true;
                    lU = new String[lUWithoutModelCode.length];
                    System.arraycopy(lUWithoutModelCode, 0, lU, 0, lUWithoutModelCode.length);
                    lU[0] = rsOfMC.getString(1);
                    insertDataList.add(lU);
//                    System.out.println(imageCode);
                }
                
	            if (!hasRow) {
//	            	System.out.println(imageCode + "没有用法");
	            	insertDataList.add(lUWithoutModelCode);
				}
                
                if (insertDataList.size() > 100000) {
                    insertData = new String[insertDataList.size()][];
                    for (int i = 0; i < insertDataList.size(); i++) {
                        insertData[i] = insertDataList.get(i);
                    }
                    sqlRunner.batch(connection, insertString, insertData);
                    insertDataList.clear();
                }
            }
            
            //剩下的
            insertData = new String[insertDataList.size()][];
            for (int i = 0; i < insertDataList.size(); i++) {
                insertData[i] = insertDataList.get(i);
            }
            sqlRunner.batch(connection, insertString, insertData);
            
        } catch (Exception e) {
            throw e;
        } finally {
            DbUtils.closeQuietly(null, queryMCstatement, rsOfMC);
            DbUtils.closeQuietly(null, queryLUstatement, rsOfLU);
        }
    }
}
